﻿using Model;
using Model.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DBContentToSqlServer
    {
        DBComd cmd = new DBComd();


        public int InsertMachineStatusTime(MachineStatusMessage data)
        {
            //int a = 0;
            string strSql = $"INSERT INTO T_MachineStatusMessage(MachineId,RunningTime,AlarmTime,StopTime,AlarmMessage,TeamGroup,OkQty,NgQty,GetTime) " +
                $"VALUES ('{data.MachineId}','{data.RunningTime}','{data.AlarmTime}','{data.StopTime}','{data.AlarmMessage}','{data.TeamGroup}','{data.OkQty}','{data.NgQty}','{data.GetTime}')";
            int a = cmd.Insert(strSql);
            if (a != 0)
            {
                return a;
            }
            else
            {
                return a; //0没有成功
            }
        }
        /// <summary>
        /// 获取设备工单排程
        /// </summary>
        /// <returns></returns>
        public List<WorkNoList> GetWorkNo(string MachineId)
        {
            //string strSql = $"select * from T_WorkData1_ZXJ Where MachineId='{MachineId}'";
            //string strSql = $"exec AP_PP_Get_ZXJ_Scheduling_SP '{MachineId}'";
            string strSql = $"EXEC [192.168.190.21].[IOTdata].[dbo].[AP_PP_Get_ZXJ_Scheduling_SP] '{MachineId}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<WorkNoList>();
            }
            else
            {
                return null;
            }
        }

        public List<WorkNoList1> GetWorkNo1(string Wono)
        {
            //string strSql = $"select * from T_WorkData1_ZXJ Where m_itemno='{Wono}'";
            //string strSql = $"exec AP_PP_Get_ZXJ_Scheduling_SP '{MachineId}'";
            string strSql = $"EXEC [192.168.190.21].[IOTdata].[dbo].[DCS_Get_matnr_CCF_SP] '{Wono}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<WorkNoList1>();
            }
            else
            {
                return null;
            }
        }

        public List<CZRYModel> GetCZRY(string MachineId)
        {
            //string strSql = $"select EmpId as usr_name from T_WorkData_ZXJ where MachineId='{MachineId}' Order by GetTime desc";
            string strSql = $"EXEC [192.168.190.21].[IOTdata].[dbo].[App_GetMachineWorker_SP] '{MachineId}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<CZRYModel>();
            }
            else
            {
                return null;
            }
        }

        public List<ZZModel> GetZZ(string MachineId)
        {
            //string strSql = $"select EmpId as usr_name from T_WorkData_ZXJ where MachineId='{MachineId}' Order by GetTime desc";
            string strSql = $"EXEC [192.168.190.21].[IOTdata].[dbo].[App_GetMachineManager_SP] '{MachineId}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<ZZModel>();
            }
            else
            {
                return null;
            }
        }

        public List<SBBYModel> GetSBBY(string MachineId)
        {
            //string strSql = $"select EmpId as usr_name from T_WorkData_ZXJ where MachineId='{MachineId}' Order by GetTime desc";
            string strSql = $"EXEC [192.168.190.21].[IOTdata].[dbo].[App_GetMachineMaintainer_SP] '{MachineId}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<SBBYModel>();
            }
            else
            {
                return null;
            }
        }
        public int InsertDJ(DJDataModel data)
        {
            string strSql = $"INSERT INTO [dbo].[T_MachineDJ_ZXJ]([MachineId],[JJTZKG],[JSQ],[RJJM],[XJTX],[FGJP],[ZDMD],[THJC],[BTHJC],[THZZ],[JZQG],[AQMZZ],[JTBMZW],[GQGYZZ],[JTSFLQ],[QYB],[YSKQ],[DZDJ],[SDZH],[XRHBW],[ZDP],[TRCDBF],[PD],[LDJC],[CZRY],[ZZ],[JXBQ],[DJDate],[GetTime],[DJType]) " +
                $"VALUES('{data.MachineId}','{data.JJTZKG}','{data.JSQ}','{data.RJJM}','{data.XJTX}','{data.FGJP}','{data.ZDMD}','{data.THJC}','{data.BTHJC}','{data.THZZ}','{data.JZQG}','{data.AQMZZ}','{data.JTBMZW}','{data.GQGYZZ}','{data.JTBMZW}','{data.QYB}','{data.YSKQ}'" +
                $",'{data.DZDJ}','{data.SDZH}','{data.XRHBW}','{data.ZDP}','{data.TRCDBF}','{data.PD}','{data.LDJC}','{data.CZRY}','{data.ZZ}','{data.JXBQ}','{data.DJDate}','{data.GetTime}','{data.DJType}') ";
            int a = cmd.Insert(strSql);
            if (a != 0)
            {
                return a;
            }
            else
            {
                return a; //0没有成功
            }
        }
        /// <summary>
        /// 获取当天是否有点检记录
        /// </summary>
        /// <returns></returns>
        public bool GetDayData()
        {
            //查询当天是否有一条点检记录，查询八点后的数据
            string strSql = $"SELECT * FROM T_MachineDJ_ZXJ " +
                $"WHERE CAST(GetTime AS DATE) = CAST(GETDATE() AS DATE)AND DATEPART(HOUR, GetTime) >= 8 " +
                $"ORDER BY GetTime DESC";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<DJDataModelList> GetDJList()
        {
            string strSql = $"select * from T_MachineDJ_ZXJ ORDER BY GetTime DESC";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<DJDataModelList>();
            }
            else
            {
                return null;
            }
        }

        public List<ZXJDataModel> GetDJData()
        {
            string strSql = $"select * from T_MachineDJ_ZXJ Where IsOutPut=0 ORDER BY GetTime DESC";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<ZXJDataModel>();
            }
            else
            {
                return null;
            }
        }

        public int UpdateData(List<string> ids)
        {
            //批量修改
            if (ids == null || ids.Count == 0) return 0;
            string strSql = $"Update T_MachineDJ_ZXJ SET IsOutPut = 1 Where ID IN (";
            // 使用参数化 SQL（避免注入）
            for (int i = 0; i < ids.Count; i++)
            {
                strSql += $"'{ids[i]}',";
            }
            strSql = strSql.Substring(0, strSql.Length - 1) + ")";
            int dt = cmd.Update(strSql);
            if (dt>0)
            {
                return dt;
            }
            else
            {
                return 0;
            }
            
        }

        public List<DJDataModelList> GetDJList1(string Time)
        {
            string strSql = $"select * from T_MachineDJ_ZXJ where GetTime ='{Time}' ORDER BY GetTime DESC";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<DJDataModelList>();
            }
            else
            {
                return null;
            }
        }
        public List<DJDayDataModelList> GetDayDJList(SearchModel data)
        {
            string strSql = $@"exec sp_GetDayDJList " +
                  $"@StartTime = '{data.StartTime:yyyy-MM-dd HH:mm:ss}', " +
                  $"@EndTime = '{data.EndTime:yyyy-MM-dd HH:mm:ss}', " +
                  $"@OperatorName = {(string.IsNullOrEmpty(data.CZRY) ? "NULL" : $"'{data.CZRY}'")}";
            //string strSql = $"select MachineId,CZRY,GetTime from T_MachineDJ_ZXJ ORDER BY GetTime DESC";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<DJDayDataModelList>();
            }
            else
            {
                return null;
            }
        }

        public List<TimerModel> GetTimer(TimerSearchModel data)
        {
            //string strSql = $"select * from T_MachineStatusMessage Order by GetTime desc";
            string strSql = $@"exec SummarizeMachineStatus " +
                 $"@StartTime = '{data.StartTime:yyyy-MM-dd}', " +
                 $"@EndTime = '{data.EndTime:yyyy-MM-dd}'";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<TimerModel>();
            }
            else
            {
                return null;
            }
        }
        public List<MMachineStateCNC> GetMachineStateDataByCNC()
        {
            string strSql = "Exec GetMachineState";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachineStateCNC>();
            }
            else
            {
                return null;
            }
        }

        public List<MMachineCapactiyCNC> GetMachineCapactiyDataByCNC()
        {
            string strSql = "Exec GetMachineCapactiy";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachineCapactiyCNC>();
            }
            else
            {
                return null;
            }
        }

        public List<MMachineProgram> GetMachineProgramDataByCNC()
        {
            string strSql = "Exec GetMachineProgram";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachineProgram>();
            }
            else
            {
                return null;
            }
        }

        public List<MMachinePressure> GetMachinePressureDataByCNC()
        {
            string strSql = "Exec GetMachinePressure";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachinePressure>();
            }
            else
            {
                return null;
            }
        }
        public List<MMachinetemperature> GetMachinetemperatureDataByCNC()
        {
            string strSql = "Exec GetMachinetemperature";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachinetemperature>();
            }
            else
            {
                return null;
            }
        }
        public List<MMachineAlarmCNC> GetMachineAlarmDataByCNC()
        {
            string strSql = "Exec GetMachineAlarm";
            DataTable dt = new DataTable();
            dt = cmd.GetTData(strSql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.TableToModel<MMachineAlarmCNC>();
            }
            else
            {
                return null;
            }
        }
    }

}
